package org.example.volunteerend.mapper;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

@Mapper
public interface HomeMapper {
    
    /**
     * 获取首页轮播项目
     */
    @Select("SELECT project_id, project_name, cover_image_url, description " +
            "FROM volunteer_projects " +
            "WHERE status = '招募中' AND cover_image_url IS NOT NULL " +
            "ORDER BY start_time DESC " +
            "LIMIT 5")
    List<Map<String, Object>> getBannerProjects();
    
    /**
     * 获取热门项目（按点赞数排序）
     */
    @Select("SELECT project_id, project_name, cover_image_url, start_time, end_time, " +
            "location, current_volunteers, max_volunteers, likes " +
            "FROM volunteer_projects " +
            "WHERE status = '招募中' " +
            "ORDER BY likes DESC, start_time ASC " +
            "LIMIT 6")
    List<Map<String, Object>> getHotProjects();
    
    /**
     * 获取平台统计数据
     */
    @Select("SELECT " +
            "(SELECT COUNT(*) FROM users) AS total_users, " +
            "(SELECT COUNT(*) FROM volunteer_projects) AS total_projects, " +
            "(SELECT COALESCE(SUM(service_hours), 0) FROM service_records) AS total_service_hours")
    Map<String, Object> getStatistics();
    
    /**
     * 获取基于用户兴趣的推荐项目
     */
    @Select("SELECT p.project_id, p.project_name, p.cover_image_url, p.start_time, p.end_time, " +
            "p.location, p.current_volunteers, p.max_volunteers, p.status, c.category_name " +
            "FROM volunteer_projects p " +
            "JOIN project_category_mapping pcm ON p.project_id = pcm.project_id " +
            "JOIN project_categories c ON pcm.category_id = c.category_id " +
            "JOIN user_interest_categories uic ON c.category_id = uic.category_id " +
            "WHERE p.status = '招募中' AND uic.user_id = #{userId} " +
            "GROUP BY p.project_id " +
            "ORDER BY p.start_time ASC " +
            "LIMIT #{limit}")
    List<Map<String, Object>> getRecommendedProjects(Integer userId, Integer limit);
} 